/*
 DNN Content Builder - sourceforge.net/projects/dnncb
 Copyright (c) 2004-2008
 by Emerson Lopes (emersonlopes@gmail.com)

 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
 documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
 the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
 to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 The above copyright notice and this permission notice shall be included in all copies or substantial portions 
 of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
 TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
 THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
 CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
 DEALINGS IN THE SOFTWARE.
*/
using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DNNContentBuilder.UserControls.FieldControls;

namespace DNNContentBuilder
{
	/// <summary>
	/// Used by the Linker control.
	/// </summary>
	public class LinkerController
	{
		#region Member Variables
		private readonly FieldDefinitionInfo fieldDefinition;
		private readonly DataTable dataTable;
		private readonly ConnectionStringInfo csInfo;
		private readonly DataConnectionInfo connectionInfo;
		private readonly string foreignKeyValue;
		#endregion

		#region Constructors
		/// <summary>
		/// Initializes a new instance of the <see cref="LinkerController"/> class.
		/// </summary>
		/// <param name="fieldDefinition"></param>
		/// <param name="foreignKeyValue"></param>
		public LinkerController(FieldDefinitionInfo fieldDefinition, string foreignKeyValue)
		{
			this.fieldDefinition = fieldDefinition;
			this.foreignKeyValue = foreignKeyValue;
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="LinkerControl"/> class
		/// that is going to be used for database update operations.
		/// </summary>
		/// <param name="fieldDefinition"></param>
		/// <param name="dataTable"></param>
		/// <param name="csInfo"></param>
		/// <param name="connectionInfo"></param>
		/// <param name="foreignKeyValue"></param>
		public LinkerController(FieldDefinitionInfo fieldDefinition, DataTable dataTable, ConnectionStringInfo csInfo, DataConnectionInfo connectionInfo, string foreignKeyValue)
		{
			this.fieldDefinition = fieldDefinition;
			this.dataTable = dataTable;
			this.csInfo = csInfo;
			this.connectionInfo = connectionInfo;
			this.foreignKeyValue = foreignKeyValue;
		}
		#endregion

		#region Properties
		/// <summary>
#pragma warning disable 1574
		/// Gets the name of the table the <see cref="LinkController"/>
#pragma warning restore 1574
		/// is handling.
		/// </summary>
		public string TableName
		{
			get
			{
				return this.fieldDefinition.TableName;
			}
		}
		#endregion

		#region Implementation
		/// <summary>
		/// Gets data from the table the system links to.
		/// </summary>
		/// <returns></returns>
		public DataTable GetByLinkedTable()
		{
			string tableName = this.TableName;

			if (tableName == null)
			{
				return null;
			}

			// Retrieve Connection String
			string connectionString = Commons.GetConnectionString(this.fieldDefinition.FormDefinitionId);
			
			ArgumentValidator.AssertNotNull(connectionString, "INVALID_FORM_DEFINITION_CONNSTRING");

			// Fill the result table
			string cmdText = GetSelectCommandText(tableName, foreignKeyValue);

			DataTable dt = new DataTable();

			using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, connectionString))
			{
				adapter.Fill(dt);
			}

			// Massage the table schema. Data remains untouched.
			dt = LinkerControl.Adapter.GetTableSchema(dt, this.fieldDefinition.FormDefinitionId, this.TableName);

			return dt;
		}

		/// <summary>
		/// Builds a SQL command string that will return a cursor containing 
		/// a <b>Title</b> column and and a <b>RowOrder</b> column.
		/// </summary>
		/// <returns>The SQL command string</returns>
		public string GetSelectCommandText(string tableName, string foreignKeyValue)
		{
			string commandText = @"select {0} from {1} where {2}";

			string fieldList   = GetFieldList(tableName);
			string whereClause = GetWhereClause();

			commandText = String.Format(commandText, fieldList, tableName, whereClause);
			
			return commandText;
		}

		public string GetWhereClause()
		{
			return String.Format("{0} = '{1}'", fieldDefinition.FieldName, this.foreignKeyValue);
		}

		public string GetFieldList(string tableName)
		{
			bool pkFound = false, titleFound = false;

			StringBuilder sb = new StringBuilder();
			ArrayList fields = new DbContentController(this.fieldDefinition.FormDefinitionId).GetFields(this.TableName);

			foreach (FieldDefinitionInfo fdInfo in fields)
			{
				sb.AppendFormat("{0} as [{0}],", fdInfo.SourceField);

				if (fdInfo.IsPrimaryKey && !pkFound)
				{
					sb.AppendFormat("{0} as {1},", fdInfo.SourceField, LinkerControl.IDColumnName);
					pkFound = true;
				}

				if (fdInfo.IsTitle && !titleFound)
				{
					sb.AppendFormat("{0} as {1},", fdInfo.SourceField, LinkerControl.TitleColumnName);
					titleFound = true;
				}
			}

			// Remove trailing comma
			if (sb.Length > 0)
			{
				sb.Remove(sb.Length - 1, 1);
			}

			return sb.ToString();
		}

		public void Update()
		{
			using (OleDbConnection conn = new OleDbConnection(csInfo.ConnectionString))
			{
				conn.Open();

				using (OleDbTransaction transaction = conn.BeginTransaction())
				{
					DeleteOldItems(transaction);

					foreach (DataRow dr in dataTable.Rows)
					{
						InsertRow(transaction, dr);
					}
					
					transaction.Commit();
				}
			}
		}

		private void DeleteOldItems(OleDbTransaction transaction)
		{
			using (OleDbCommand command = transaction.Connection.CreateCommand())
			{
				command.Transaction = transaction;

				command.CommandText = String.Format("DELETE FROM {0} WHERE {1} = '{2}'", 
					connectionInfo.TableName,
					fieldDefinition.FieldName,
					foreignKeyValue);

				command.ExecuteNonQuery();
			}
		}

		private void InsertRow(OleDbTransaction transaction, DataRow row)
		{
			using (OleDbCommand command = transaction.Connection.CreateCommand())
			{
				command.Transaction = transaction;

				StringBuilder into   = new StringBuilder();
				StringBuilder values = new StringBuilder();
				const string insertCommandText = "INSERT INTO {0} ({1}) VALUES ({2})";

				foreach (DataColumn column in dataTable.Columns)
				{
					if (column.Expression.Length > 0)
					{
						continue; // Ignore expression columns
					}
				
					into.AppendFormat("{0},", column.ColumnName);
					values.AppendFormat("?,");

					OleDbParameter param = GetCommandParameter(column, row);

					command.Parameters.Add(param);
				}

				if (into.Length   > 0) into.Remove(into.Length - 1, 1);
				if (values.Length > 0) values.Remove(values.Length - 1, 1);

				command.CommandText = String.Format(insertCommandText, fieldDefinition.TableName, into.ToString(), values.ToString());
				command.CommandType = CommandType.Text;

				command.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// This version handles all field types other than primary key fields
		/// </summary>
#pragma warning disable 1574
		/// <param name="fdInfo"></param>
#pragma warning restore 1574
		/// <returns></returns>
		private OleDbParameter GetCommandParameter(DataColumn column, DataRow row)
		{
			OleDbParameter result = new OleDbParameter();

			// Only for reference; OleDB does not support named parameters
			result.ParameterName = column.ColumnName;

			result.DbType = TypeEvaluator.GetFieldDbType(column.DataType);
			result.Value  = row[column];

			LinkProxy linkProxy = new LinkProxy(this.fieldDefinition);
			
			if (linkProxy.IsMatch(column.ColumnName))
			{
				if (result.DbType == DbType.Guid)
				{
					result.Value = new Guid(this.foreignKeyValue);
				}
				else
				{
					result.Value = this.foreignKeyValue;
				}
			}

			return result;
		}
		#endregion
	}
}
